package com.neuedu.util;


import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 项目：      jdbc
 * 类名:       JDBCUtil
 * 创建时间：  2020/11/26  13:55
 * 描述 :
 * 作者 :     张金山
 * QQ :     314649444
 * Site:      https://jshand.gitee.io
 */
public class JDBCUtil {


    private static String driver ;
    //连接的数据 mysql的默认端口 3306  Oracle 1521
    private static String url ;
    //连接数据库的用户名、密码
    private static String username;
    private static String password ;


    static {
        try {

            ClassLoader cl = Thread.currentThread().getContextClassLoader();
            InputStream in = cl.getResourceAsStream("jdbc.properties");

            Properties properties = new Properties();
            properties.load(in);

            url = properties.getProperty("jdbc.url");
            driver = properties.getProperty("jdbc.driverClass");
            username = properties.getProperty("jdbc.username");
            password = properties.getProperty("jdbc.password");


            Class.forName(driver);


        }catch (Exception e) {
            System.out.println("数据库连接异常,请检查 1 是否提供jdbc.properties文件, 2 属性文件中是否正确");
            e.printStackTrace();
        }
    }


    /**
     * 加载数据库连接
     *
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private static Connection getConnection() throws ClassNotFoundException, SQLException {

        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }


    private static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void close(PreparedStatement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void close(ResultSet rs, PreparedStatement stmt, Connection conn) {
        close(rs);
        close(stmt);
        close(conn);
    }

    /**
     * 查询集合
     *
     * @param sql
     * @param rowMap 转换一行的 方法
     * @param <T>    每行结果转换后的  类型 实体
     * @return 集合
     */
    public static <T> List<T> executeQuery(String sql, RowMap<T> rowMap, Object... param) {
        List list = new ArrayList();

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = conn.prepareStatement(sql);
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    stmt.setObject((i + 1), param[i]);
                }
            }
            rs = stmt.executeQuery();
            while (rs.next()) {
                //解析每一行
                Object obj = rowMap.rowmap(rs);
                list.add(obj);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            close(rs, stmt, conn);
        }
        return list;
    }


    //查询一条
    public static <T> T getOne(String sql, RowMap<T> rowMap, Object... param) {

        List<T> list = executeQuery(sql, rowMap, param);
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }


    public static <T> T getOne(String sql, Class clazz, Object... param) {

        List<T> list = executeQuery(sql, clazz, param);
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    //执行更新语句
    public static boolean executeUpdate(String sql, Object... param) {

        Connection conn = null;
        PreparedStatement stmt = null;
        int count = 0;
        try {
            conn = getConnection();
            stmt = conn.prepareStatement(sql.toString());

            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    stmt.setObject((i + 1), param[i]);
                }
            }
            count = stmt.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            close(null, stmt, conn);
        }
        return count > 0;
    }


    public static <T> List<T> executeQuery(String sql, Class clazz, Object... param) {
        List list = new ArrayList();

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = conn.prepareStatement(sql);
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    stmt.setObject((i + 1), param[i]);
                }
            }
            rs = stmt.executeQuery();
            ResultSetMetaData meta = rs.getMetaData();
            int count = meta.getColumnCount();
            while (rs.next()) {
                //解析每一行

                Object obj = clazz.newInstance();
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {

                    field.setAccessible(true);
                    try {

                        //@Column  account_name
                        String name = field.getName();
                        if (field.isAnnotationPresent(Column.class)) {//如果属性上有有Column注解，则使用Column的属性作为字段名
                            Column column = field.getAnnotation(Column.class);
                            name = column.value();
                        }

                        field.set(obj, rs.getObject(name));
                    } catch (Exception e) {
                        System.out.println("没有字段" + field.getName());
                    }

                }
                list.add(obj);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {

            close(rs, stmt, conn);
        }
        return list;
    }


    /**
     * 查询总条数
     * @param sql
     * @param args
     * @return
     */
    public static long executeQueryCount(String sql,Object... args){
        List list = new ArrayList();

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = conn.prepareStatement(sql);
            if (args != null) {
                for (int i = 0; i < args.length; i++) {
                    stmt.setObject((i + 1), args[i]);
                }
            }
            rs = stmt.executeQuery();

            rs.next();
            return rs.getLong(1);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            close(rs, stmt, conn);
        }
        return 0;
    }









}